Troubleshoot editing data

Absolute or relative cell references    If the copied formulas contain relative cell references, Microsoft Excel adjusts the references (and the relative parts of mixed cell references) in the duplicate formulas. For example, suppose cell B8 contains the formula =SUM(B1:B7). If you copy the formula to the cell C8, the duplicate formula refers to the corresponding cells in that column: =SUM(C1:C7). If the copied formulas contain absolute cell references, the references in the duplicate formulas are not changed.

When you need to redo the move or copy    If you did not get the results you want, change the references in the original formulas to either relative or absolute cell references. Then recopy the cells.

Match entire cells only    On the Edit menu, click Find or Replace. Clear the Match entire cell contents check box, and then try the search again. When this check box is selected, the Find and Replace commands find only cells that contain the characters specified in the Find what box. Cells that contain additional characters are not found.

Clear Find or Replace format settings    To search for unformatted text or numbers, make sure to clear any format settings in the Find Format dialog box before you begin your search.

Check the entire worksheet or workbook Microsoft Excel searches only within the selected range of cells.

• To search the entire sheet, click any cell to cancel the active selection before you use the Find or Replace command.

• To search an entire workbook, click Find on the Edit menu, click Options, and then select Workbook in the Within box.

Check in formulas and comments    If the characters you want to search for are a constant value or a value that results from a formula, click Options in the Find dialog box, and then click Values in the Look in box. If the characters are in a formula or in cell comments, click Formulas or Comments in the Look in box.

Note  When you use the Replace tab in the Find and Replace dialog box, Excel looks in formula only, therefore the Look in box is not available under Replace.

Hide special characters    If an asterisk (*), caret (^), quotation mark ("), or backslash (\) appears in the formula bar but not in the worksheet cells, the character is a formatting code. Because such characters are not actual data, you cannot use the Find and Replace commands to locate them.

To hide the characters

• Click Options on the Tools menu, click the Transition tab, and then clear the Transition navigation keys check box.

Some actions can't be undone, such as any command on the File menu. The Undo command is available for each action you can undo.

Note  Macros can clear all items from the undo list.

You can copy and paste only one selection at a time. To ensure that the selection you are copying stays selected, do not copy any other selection.

Note You can use the Microsoft Office Clipboard to collect various items and then paste them.

If values such as numbers or dates are incremented through the selected range instead of copied when you drag the fill handle, click Auto Fill Options and click Copy Cells.